﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Xml;
using ClassTester.ConditionalFormatting;
using ClassTester.FormulaBuilder;
using ClassTester.UtilClasses;
using Microsoft.Office.Interop.Excel;
using OfficeOpenXml;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Drawing.Chart;
using OfficeOpenXml.Style;
using Excel = Microsoft.Office.Interop.Excel;
using Range = Microsoft.Office.Interop.Excel.Range;


namespace ClassTester
{
    public static class ToetsEvalCreator
    {
        private static  int aantalStudenten = 0;
        private static int aantalVragen = 0;

        public static void CreateExcel(string filename, int aantalstudentenin, int aantalvragen, string sheetname="exam eval"  )
        {
            //TODO: Toon warning dat er geen histograms gemaakt zullenworden
            var gewichten= new List<Vraag>();
            for (int i = 0; i < aantalVragen; i++)
            {
                gewichten.Add(new Vraag(){Gewicht = 1});
            }
            CreateExcel(filename,aantalstudentenin,gewichten,false, sheetname);
        }
   /// <summary>
   /// Create  excel sheet 
   /// </summary>
   /// <param name="filename"></param>
   /// <param name="aantalstudentenin">No of students</param>
   /// <param name="gewichten">Weight per question</param>
   /// <param name="containsGewichten">Set to true if you need histograms (only works if weight/question is already known)</param>
   /// <param name="sheetname"></param>
        public static void CreateExcel(string filename, int aantalstudentenin, List<Vraag> gewichten,bool containsGewichten, string sheetname="exam eval", bool beschermsheet=false   )
        {
            var newFile = CreateFile(filename);
            aantalStudenten = aantalstudentenin;
            aantalVragen = gewichten.Count;

            using (ExcelPackage package = new ExcelPackage(newFile))
            {//http://nl.wikipedia.org/wiki/Lijst_van_Excel-functies


                // add a new worksheet to the empty workbook
                var ws = package.Workbook.Worksheets.Add(sheetname);

                ws.DefaultColWidth = 7;
                
               CreateMainArray(ws,1,1, gewichten);//Don't change

                AddGraphSheet(package, ws,gewichten);

 
                //Statistieken
               CreateStatisticsArray(ws,5,1, gewichten);
               
                ws.Column(1).AutoFit(10.0);
                
                CreateHeaderFooter(ws);
                
                ws.Cells[20 + 2 + aantalStudenten + 9 + 2, 1 + 4].Style.Locked = false;
                package.Save();
                
            }

           if (beschermsheet)
           {
           //    ProtectWorkbook(newFile);
           }
            
           AddConditionalFormattingAndStatistics(filename, gewichten);

           
        }

        private static void ProtectWorkbook(FileInfo newFile)
        {
            
            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                package.Workbook.Protection.LockStructure = true;
               // package.Workbook.Protection.SetPassword(password);

                //ugly: arrayformules mogen psies ni aangepast worden ://
                package.Workbook.Worksheets[1].Cells[20 + aantalStudenten + 1, 2, 20 + 2 + aantalStudenten + 2 + 9, 1 + 9].Style.Locked = false;
                //ugly!!:underscore mag wel aangepast worden
                package.Workbook.Worksheets[1].Cells[20 + 2 + aantalStudenten + 9 + 2, 1 + 4].Style.Locked = false;
                //ws.Cells[rowoffset, coloffset + aantalVragen + 2]

                foreach (var excelWorksheet in package.Workbook.Worksheets)
                {
                 //   SetProtection(excelWorksheet, password);
                }

                //Ugly!!!!

                package.Save();
            }
        }

        private static void CreateHeaderFooter(ExcelWorksheet ws)
        {
            ws.HeaderFooter.FirstHeader.CenteredText = "Betrouwbaarheid van een toets";
            ws.HeaderFooter.FirstFooter.RightAlignedText = "Voor meer informatie: pieter.depessemier@artesis.be";
            FileInfo f = new FileInfo("Images\\logo.png");
            ws.HeaderFooter.FirstHeader.InsertPicture(f, PictureAlignment.Left);
        }

        private static void AddConditionalFormattingAndStatistics(string filename, List<Vraag> gewichten)
        {

            //http://msdn.microsoft.com/en-us/library/bb404903.aspx
            var exApp = new Excel.Application();
            Workbooks wk=null;
            Workbook wb = null;
            Worksheet ws = null; 
            try
            {
                //http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c-sharp
                // http://www.velocityreviews.com/forums/showpost.php?s=f87f0674feda4442dcbd40019cbca65b&p=528575&postcount=2
                 wk = exApp.Workbooks;
                 wb = wk.Open(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                                   Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                                   Missing.Value,
                                                   Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                                   Missing.Value);
                ws = wb.Sheets[1];
                AddConditionalStatistics(ws, gewichten, 20, 1);

                //lets add conditional formatting
                AddConditionalFormatting(ws, 5, 1);
                AddConditionalFormatting(ws, 20, 1);



                //protec this sheet
                //dont protect underscore
                //   ws.get_Range(ws.Cells[1, 1].Address, ws.Cells[20, 20].Address).Locked = true;
              //  ws.get_Range(ws.Cells[20 + 2 + aantalStudenten + 9 + 2, 1 + 4].Address, ws.Cells[20 + 2 + aantalStudenten + 9 + 2, 1 + 4].Address).Locked = false;
                 
               ws.Protect(, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, true, true);

            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                throw (ex);
            }
            finally
            {
                if (ws != null) { Marshal.ReleaseComObject(ws); }
                if (wb != null) { 
                    wb.Save();
                    wb.Close(); 
                    Marshal.ReleaseComObject(wb); }
               
               if(wk!=null) {wk.Close(); Marshal.ReleaseComObject(wk);}

                exApp.Quit();    
                try
                {
                    Marshal.ReleaseComObject(exApp);
                }
                finally
                {
                    exApp = null;
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                }
            }
            
        }

        private static void AddConditionalStatistics(Worksheet ws, List<Vraag> gewichten, int rowoffset, int coloffset)
        {
            

                
                string underscore = ws.Cells[rowoffset + 2 + aantalStudenten + 9 + 2, coloffset + 4].Address;//ws.Cells[rowoffset, coloffset + aantalVragen + 2]
                    
            
                string fullscore = string.Format("{0}:{1}", ws.Cells[1 + 2, coloffset + aantalVragen+2].Address,
                                                        ws.Cells[1 + 2 + aantalStudenten - 1, coloffset + aantalVragen + 2].Address);
                string test = string.Format("{0} > {1}", fullscore, underscore);

                //Variantie  //=VAR(   ALS   ( R3:R17> D33;B3:B17;ONWAAR) )
                int offset = 0;
                ws.Cells[rowoffset + 2 + aantalStudenten + offset, 1].Value = "Variantie";
                for (int i = 1; i <= aantalVragen + 2; i++)
                {
                    string answercolumn = string.Format("{0}:{1}", ws.Cells[1 + 2, coloffset + i].Address,
                                                        ws.Cells[1 + 2 + aantalStudenten - 1, coloffset + i].Address);

                    string ifstr= string.Format(FormBuilder.IF(test, answercolumn, "FALSE"));
                    string form =string.Format("=VAR({0})",ifstr);
              //      Debug.WriteLine(form);
                    Range r = ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i];
                    r.FormulaArray = form;
         
                }
                offset++;


                //correlatie
                //ALS.FOUT(CORRELATIE((ifstr1);(ifstr2)-(ifstr1));"")

                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "RIR";
                for (int i = 1; i <= aantalVragen; i++)
                {
             

                    string answercolumn = string.Format("{0}:{1}", ws.Cells[1 + 2, coloffset + i].Address,
                                        ws.Cells[1 + 2 + aantalStudenten - 1, coloffset + i].Address);
                    string fullscorenietop20 =string.Format("{0}:{1}", ws.Cells[1 + 2, coloffset + aantalVragen + 2-1].Address,
                                                        ws.Cells[1 + 2 + aantalStudenten - 1, coloffset + aantalVragen + 2-1].Address);

                    string ifstr1 = string.Format(FormBuilder.IF(test, answercolumn, "FALSE"));
                    string ifstr2 = string.Format(FormBuilder.IF(test, fullscorenietop20, "FALSE"));
                    string corrstring =string.Format("CORREL({0},{1}-{0})",ifstr1,ifstr2);
                    string form = "="+ FormBuilder.IFERROR(corrstring);
                  //  Debug.WriteLine(form);
                    Range r = ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i];
                    r.FormulaArray = form;
                }
                ;

                offset++;


                //pwaarde
                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "P-waarde";
                for (int i = 1; i <= aantalVragen; i++)
                {
                    ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i].Formula=("=IFERROR(" + ws.Cells[
                                                                                         2 + rowoffset + aantalStudenten + offset + 4, coloffset + i].
                                                                                         Address + "/" +
                                                                                     ws.Cells[1, coloffset + i].Address
                                                                                      + ",\"\")"
                        );
                }
                string parray = string.Format("{0}:{1}", ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + 1].Address,
                                              ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + aantalVragen].Address);

                offset++;


                //pwaarde voor mc
                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "MC p-waarde ";
                for (int i = 1; i <= aantalVragen; i++)
                {
                    string originalp = ws.Cells[rowoffset + 2 + aantalStudenten + offset - 1, coloffset + i].Address;
                    string aantalmc = ws.Cells[2 + aantalStudenten + 1, coloffset + i].Address;
                    if (i - 1 <= gewichten.Count && gewichten[i - 1].IsMultipleChoice)
                    {
                        string form = string.Format(@"={0}-((1-{0})/({1}-1))", originalp, aantalmc);
                        ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i].Formula = form;//p’ = p-[(1-p)/(a-1)] 
                    }
                    else
                    {
                        Range r = ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i];
                         //.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        r.Interior.Color = Color.FromArgb(0, 0, 0); //   ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(0, 0, 0));
                    }

                }
                offset++;
                //mediaan

                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "MediaanNieuw";
                for (int i = 1; i <= aantalVragen + 2; i++)
                {
                    string answercolumn = string.Format("{0}:{1}", ws.Cells[1 + 2, coloffset + i].Address,
                                    ws.Cells[1 + 2 + aantalStudenten - 1, coloffset + i].Address);

                    string ifstr = string.Format(FormBuilder.IF(test, answercolumn, "FALSE"));
                    string form = string.Format("=MEDIAN({0})", ifstr);
                    //      Debug.WriteLine(form);
                    Range r = ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i];
                    r.FormulaArray = form;
                }
                offset++;


                //modus

                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "Modus";
                for (int i = 1; i <= aantalVragen + 2; i++)
                {
                    string answercolumn = string.Format("{0}:{1}", ws.Cells[1 + 2, coloffset + i].Address,
                                    ws.Cells[1 + 2 + aantalStudenten - 1, coloffset + i].Address);

                    string ifstr = string.Format(FormBuilder.IF(test, answercolumn, "FALSE"));
                    string form = string.Format("=MODE({0})", ifstr);
                    //      Debug.WriteLine(form);
                    Range r = ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i];
                    r.FormulaArray = form;
                }
                offset++;

                //Gemiddelde


                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "Gemiddelde";
                for (int i = 1; i <= aantalVragen + 2; i++)
                {
                    string answercolumn = string.Format("{0}:{1}", ws.Cells[1 + 2, coloffset + i].Address,
                                    ws.Cells[1 + 2 + aantalStudenten - 1, coloffset + i].Address);

                    string ifstr = string.Format(FormBuilder.IF(test, answercolumn, "FALSE"));
                    string form = string.Format("=AVERAGE({0})", ifstr);
                    //      Debug.WriteLine(form);
                    Range r = ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i];
                    r.FormulaArray = form;
                }
                offset++;


                //sd


                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "SD";
                for (int i = 1; i <= aantalVragen + 2; i++)
                {
                    string answercolumn = string.Format("{0}:{1}", ws.Cells[1 + 2, coloffset + i].Address,
                                     ws.Cells[1 + 2 + aantalStudenten - 1, coloffset + i].Address);

                    string ifstr = string.Format(FormBuilder.IF(test, answercolumn, "FALSE"));
                    string form = string.Format("=STDEV({0})", ifstr);
                    //      Debug.WriteLine(form);
                    Range r = ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i];
                    r.FormulaArray = form;
                }
                offset++;

                //kurtosis

                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "Kurtosis";
                for (int i = 1; i <= aantalVragen + 2; i++)
                {
                    string answercolumn = string.Format("{0}:{1}", ws.Cells[1 + 2, coloffset + i].Address,
                                     ws.Cells[1 + 2 + aantalStudenten - 1, coloffset + i].Address);

                    string ifstr = string.Format(FormBuilder.IF(test, answercolumn, "FALSE"));
                    string form = string.Format("=KURT({0})", ifstr);
                    //      Debug.WriteLine(form);
                    Range r = ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i];
                    r.FormulaArray = form;
                }
                offset++;

                //scheefheid


                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "Scheefheid";
                for (int i = 1; i <= aantalVragen + 2; i++)
                {
                    string answercolumn = string.Format("{0}:{1}", ws.Cells[1 + 2, coloffset + i].Address,
                                     ws.Cells[1 + 2 + aantalStudenten - 1, coloffset + i].Address);

                    string ifstr = string.Format(FormBuilder.IF(test, answercolumn, "FALSE"));
                    string form = string.Format("=SKEW({0})", ifstr);
                    //      Debug.WriteLine(form);
                    Range r = ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i];
                    r.FormulaArray = form;
                }
                offset++;

                //cronbach's alfa
                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "Cronbach's alfa";
                string formstring = string.Format("=IFERROR( COUNTA({0}:{1})/(COUNTA({0}:{1})-1)*(1-SUM({2}:{3})/{4})   , \"\")",
                                                  ws.Cells[1, coloffset + 1].Address,
                                                  ws.Cells[1, coloffset + 1 + aantalVragen - 1].Address,
                                                  ws.Cells[2 + rowoffset + aantalStudenten, coloffset + 1].Address,
                                                  ws.Cells[2 + rowoffset + aantalStudenten, coloffset + 1 + aantalVragen - 1].Address,
                                                  ws.Cells[2 + rowoffset + aantalStudenten, coloffset + 1 + aantalVragen].Address);
                Range cr = ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + 1];
                cr.Formula = formstring;

                offset++;
                //Underscore
                
                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "Negeer scores tot en met (op 20):";
                

                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset+4].Value = 0;
                Range u = ws.Cells[rowoffset + 2 + aantalStudenten + offset , coloffset + 4];
                u.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic,
                               Color.FromArgb(250, 20, 20));
            u.Interior.Color = Color.MediumSpringGreen;
           

        }

        private static void AddConditionalFormatting(Worksheet ws, int rowoffset, int colloffset)
        {

                //Pwaarde
                Excel.Range c1 = (Excel.Range) ws.Cells[2 + aantalStudenten + rowoffset + 2, 2];
                Excel.Range c2 = (Excel.Range)ws.Cells[2 + aantalStudenten + rowoffset + 3, 2+aantalVragen-1];

                
                ConditionalFormatter.AddCondition(ws, c1, c2, ConditionalFormatter.CondError.Yellow, XlFormatConditionOperator.xlLess, "0,25");
                ConditionalFormatter.AddCondition(ws, c1, c2, ConditionalFormatter.CondError.Yellow, XlFormatConditionOperator.xlGreater, "0,85");

                //cronbachsalfas
                Excel.Range a1 = (Excel.Range)ws.Cells[2 + aantalStudenten + rowoffset + 10, 2];
                
               
                ConditionalFormatter.AddCondition(ws, a1, a1, ConditionalFormatter.CondError.Yellow, XlFormatConditionOperator.xlLess, "0,65");
                
                //RIrwaarde
                Excel.Range d1 = (Excel.Range)ws.Cells[2 + aantalStudenten + rowoffset + 1, 2];
                Excel.Range d2 = (Excel.Range)ws.Cells[2 + aantalStudenten + rowoffset + 1, 2 + aantalVragen-1];

                
                ConditionalFormatter.AddCondition(ws, d1, d2, ConditionalFormatter.CondError.Yellow, XlFormatConditionOperator.xlLess, "0,3");



        }


        private static void CreateAnswerHistograms(ExcelPackage package, ExcelWorksheet wsdata, ExcelWorksheet wssource, List<Vraag> gewichten )
        {
            var hiddenws = package.Workbook.Worksheets.Add("questionhistogramdata");
            string wsname = string.Format("'{0}'!", wsdata.Name);
            int offset = 0;
            int vraagnr = 0;
            foreach (var gewicht in gewichten)
            {
                vraagnr++;
                if (!gewicht.IsMultipleChoice)
                { 
                    offset++;
                    for (int i = 0; i <= gewicht.Gewicht; i++)
                    {
                        hiddenws.Cells[1 + i, offset*2].Value = i;
                    }
                
                   hiddenws.Hidden = eWorkSheetHidden.VeryHidden;
                    //=INTERVAL(AFRONDEN(   ALS(ISLEEG(  'exam eval'!B3:B7);MAX( 'exam eval'!B3:B7)+1;  'exam eval'!B3:B7);0);B1:B2)


                    string form = string.Format("FREQUENCY(ROUND(IF(ISBLANK({0}{1}:{2}),MAX({3}:{4})+1,{0}{1}:{2}) ,0),{3}:{4})",
                                                wsname,
                                                wsdata.Cells[3, 1 + offset].Address,
                                                wsdata.Cells[3 + aantalStudenten-1, 1 + offset].Address,
                                                hiddenws.Cells[1,  offset * 2].Address,
                                               hiddenws.Cells[gewicht.Gewicht + 1, offset * 2].Address);
                    hiddenws.Cells[1, 1 + offset * 2, gewicht.Gewicht + 1, 1 + offset * 2].CreateArrayFormula(form);
                        //1  400,1  2  400,200
                        //3  600,1	4  600,200
                        //5  800,1	6  800,200
                    var histogram = wssource.Drawings.AddChart("answerhistogram"+offset, eChartType.ColumnClustered);
                    histogram.SetPosition( 1+ (((offset-1)/2))*10, 0,10+((offset-1)%2)*5, 0); //todo: change to offset
                    
                    histogram.SetSize(200, 200);
                   histogram.Title.Text = "Histogram vraag "+ vraagnr;
                
                   string hiddenwsname = string.Format("'{0}'!", hiddenws.Name);
                   histogram.Series.Add(hiddenwsname + hiddenws.Cells[1, 1 + offset * 2, gewicht.Gewicht + 1, 1 + offset * 2].Address,
                       hiddenwsname + hiddenws.Cells[1, offset * 2, gewicht.Gewicht + 1, offset * 2].Address);
                    histogram.ShowHiddenData = true;
                    histogram.Legend.Remove();
                }
            }
        }

        private static void AddGraphSheet(ExcelPackage package, ExcelWorksheet wsdata, List<Vraag> gewichten )
        {
            
            var graphws = package.Workbook.Worksheets.Add("Grafieken");
            for (int i = 0; i <= 20; i++)
            {
                graphws.Cells[1+i, 1].Value = i;

                
            }
            //graphws.Hidden=eWorkSheetHidden.VeryHidden;
            string wsname = string.Format("'{0}'!", wsdata.Name);
            string form = string.Format("FREQUENCY({0}{1}:{2},{3}:{4})",
                                        wsname,
                                        wsdata.Cells[3, 1 + aantalVragen + 2].Address,
                                        wsdata.Cells[3 + aantalStudenten, 1 + aantalVragen + 2].Address,
                                        graphws.Cells[1, 1].Address,
                                        graphws.Cells[21, 1].Address);
            graphws.Cells[1, 2, 21, 2].CreateArrayFormula(form);
            graphws.Column(2).Hidden = true;
            graphws.Column(1).Hidden = true;


            
            var histogram = graphws.Drawings.AddChart("totalhistogram", eChartType.ColumnClustered) ;
            histogram.SetPosition(1, 0, 1, 0); //todo: change to offset
            histogram.SetSize(400, 400);
            histogram.Title.Text = "Histogram eindcijfer op 20";

           // string hiddenwsname = string.Format("'{0}'!", hiddenws.Name);
            histogram.Series.Add( graphws.Cells[1, 2, 22, 2].Address,graphws.Cells[1, 1, 22, 1].Address);
            histogram.ShowHiddenData = true;
            histogram.Legend.Remove();

            CreateAnswerHistograms(package, wsdata,graphws, gewichten );
        }


        private static void SetProtection(ExcelWorksheet ws,string password)
        {
            
            ws.Protection.IsProtected = true;
            ws.Protection.AllowSelectLockedCells = true;
            ws.Protection.AllowSort = true;
            ws.Protection.AllowFormatCells = true;
            ws.Protection.AllowFormatColumns = true;
            ws.Protection.AllowFormatRows = true;
            ws.Protection.AllowEditObject = false;
            
            
            ws.Protection.SetPassword(password);
        }
        
        private static void CreateStatisticsArray(ExcelWorksheet ws, int rowoffset, int coloffset, List<Vraag> vragen )
        {
            //Variantie
            int offset = 0;
            ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "Variantie";
            for (int i = 1; i <= aantalVragen + 2; i++)
            {
                string form = FormBuilder.IFERROR(
                    FormBuilder.VAR(ws.Cells[1 + 2, coloffset + i].Address,
                                    ws.Cells[1 + 2 + aantalStudenten - 1, coloffset + i].Address));
                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i].CreateArrayFormula(form);
            }
            offset++;

            //RIR
            ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "RIR";
            for (int i = 1; i <= aantalVragen ; i++)
            {
                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i].CreateArrayFormula("IFERROR(CORREL(" +
                                                                                 ws.Cells[3, 1 + i].Address + ":" +
                                                                                 ws.Cells[3 + aantalStudenten - 1, coloffset + i].
                                                                                     Address +
                                                                                 ",(" +
                                                                                 ws.Cells[3, coloffset + aantalVragen + 1].Address +
                                                                                 ":" +
                                                                                 ws.Cells[
                                                                                     3 + aantalStudenten - 1,
                                                                                     coloffset + aantalVragen + 1].Address +
                                                                                 ")-(" +
                                                                                 ws.Cells[3, coloffset + i].Address +
                                                                                 ":" +
                                                                                 ws.Cells[3 + aantalStudenten - 1, coloffset + i].
                                                                                     Address + ")),\"\")");
            }
            string rarray = string.Format("{0}:{1}", ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + 1].Address,
                                          ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + aantalVragen].Address);
            
            offset++;


            //pwaarde
            ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "p-waarde";
            for (int i = 1; i <= aantalVragen; i++)
            {
                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i].CreateArrayFormula("IFERROR(" + ws.Cells[
                                                                                     2 + rowoffset + aantalStudenten + offset + 4, coloffset + i].
                                                                                     Address + "/" +
                                                                                 ws.Cells[1, coloffset + i].Address
                                                                                  + ",\"\")"
                    );
            }
            string parray = string.Format("{0}:{1}", ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + 1].Address,
                                          ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + aantalVragen].Address);

            offset++;


            //pwaarde voor mc
            ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "MC P-waarde";
            for (int i = 1; i <= aantalVragen; i++)
            {
                string originalp = ws.Cells[rowoffset + 2 + aantalStudenten + offset-1, coloffset + i].Address;
                string aantalmc = ws.Cells[2 + aantalStudenten + 1, coloffset + i].Address;
                if (i-1<=vragen.Count && vragen[i-1].IsMultipleChoice)
                {
                    string form = string.Format(@"{0}-((1-{0})/({1}-1))", originalp, aantalmc);
                    ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i].Formula = form;//p’ = p-[(1-p)/(a-1)] 
                }
                else
                {
                    ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(0,0,0));
                }
                  
            }


            offset++;

            //mediaan

            ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "Mediaan";
            for (int i = 1; i <= aantalVragen + 2; i++)
            {
                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i].CreateArrayFormula("IFERROR(MEDIAN(" +
                                                                                 ws.Cells[3, coloffset + i].Address + ":" +
                                                                                 ws.Cells[3 + aantalStudenten - 1, coloffset + i].
                                                                                     Address + "),\"\")");
                //IF.ERROR werkt niet///
            }
            offset++;


            //modus

            ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "Modus";
            for (int i = 1; i <= aantalVragen + 2; i++)
            {
                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i].CreateArrayFormula("IFERROR(MODE(" +
                                                                                 ws.Cells[3, coloffset + i].Address + ":" +
                                                                                 ws.Cells[3 + aantalStudenten - 1, coloffset + i].
                                                                                     Address + "),\"\")");
                //IF.ERROR werkt niet///
            }
            offset++;

            //Gemiddelde


            ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "gemiddelde";
            for (int i = 1; i <= aantalVragen + 2; i++)
            {
                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i].CreateArrayFormula("IFERROR(AVERAGE(" +
                                                                                 ws.Cells[3, coloffset + i].Address + ":" +
                                                                                 ws.Cells[3 + aantalStudenten - 1, coloffset + i].
                                                                                     Address + "),\"\")");
                //IF.ERROR werkt niet///
            }
            offset++;


            //sd


            ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "sd";
            for (int i = 1; i <= aantalVragen + 2; i++)
            {
                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i].CreateArrayFormula("IFERROR(STDEV(" +
                                                                                 ws.Cells[3, coloffset + i].Address + ":" +
                                                                                 ws.Cells[3 + aantalStudenten - 1, coloffset + i].
                                                                                     Address + "),\"\")");
            }
            offset++;

            //kurtosis

            ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "kurtosis";
            for (int i = 1; i <= aantalVragen + 2; i++)
            {
                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i].CreateArrayFormula("IFERROR(KURT(" +
                                                                                 ws.Cells[3, coloffset + i].Address + ":" +
                                                                                 ws.Cells[3 + aantalStudenten - 1, coloffset + i].
                                                                                     Address + "),\"\")");
            
            }
            offset++;

            //scheefheid


            ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "scheefheid";
            for (int i = 1; i <= aantalVragen + 2; i++)
            {
                ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + i].CreateArrayFormula("IFERROR(skew(" +
                                                                                 ws.Cells[3, coloffset + i].Address + ":" +
                                                                                 ws.Cells[3 + aantalStudenten - 1, coloffset + i].
                                                                                     Address + "),\"\")");
                //IF.ERROR werkt niet///
            }
            offset++;

            //cronbach's alfa
            ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset].Value = "Cronbach's alfa";
            string formstring = string.Format("IFERROR( COUNTA({0}:{1})/(COUNTA({0}:{1})-1)*(1-SUM({2}:{3})/{4})   , \"\")",
                                              ws.Cells[1, coloffset + 1].Address,
                                              ws.Cells[1, coloffset + 1 + aantalVragen - 1].Address,
                                              ws.Cells[2 + rowoffset + aantalStudenten, coloffset + 1].Address,
                                              ws.Cells[2 + rowoffset + aantalStudenten, coloffset + 1 + aantalVragen - 1].Address,
                                              ws.Cells[2 + rowoffset + aantalStudenten, coloffset + 1 + aantalVragen].Address);
            ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + 1].CreateArrayFormula(formstring);

            
            //Add some style
            AddBorder(ws.Cells[rowoffset + 2 + aantalStudenten, coloffset + 1, rowoffset + 2 + aantalStudenten + offset - 1, coloffset + aantalVragen + 2], ExcelBorderStyle.DashDotDot);
            AddBorder(ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + 1, rowoffset + 2 + aantalStudenten + offset, coloffset + 1], ExcelBorderStyle.DashDotDot);
            ws.Cells[rowoffset + 2 + aantalStudenten, coloffset, rowoffset + 2 + aantalStudenten, coloffset + aantalVragen + 2].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            ws.Cells[rowoffset + 2 + aantalStudenten, coloffset, rowoffset + 2 + aantalStudenten + offset, coloffset].Style.Border.Right.Style = ExcelBorderStyle.Thin;

            ws.Cells[rowoffset + 2 + aantalStudenten, coloffset, rowoffset + 2 + aantalStudenten + offset - 1, coloffset + aantalVragen + 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
            ws.Cells[rowoffset + 2 + aantalStudenten, coloffset, rowoffset + 2 + aantalStudenten + offset - 1, coloffset + aantalVragen + 2].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 238, 185));

            ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset, rowoffset + 2 + aantalStudenten + offset, coloffset + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
            ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset, rowoffset + 2 + aantalStudenten + offset, coloffset + 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 238, 185));
            
            //Valsspelen: add style voor extra stats die we later gaan maken..maar ben te lui om het niet met epplus te doen :)

            rowoffset += 15;
            
            AddBorder(ws.Cells[rowoffset + 2 + aantalStudenten, coloffset + 1, rowoffset + 2 + aantalStudenten + offset - 1, coloffset + aantalVragen + 2], ExcelBorderStyle.DashDotDot);
            AddBorder(ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset + 1, rowoffset + 2 + aantalStudenten + offset, coloffset + 1], ExcelBorderStyle.DashDotDot);
            ws.Cells[rowoffset + 2 + aantalStudenten, coloffset, rowoffset + 2 + aantalStudenten, coloffset + aantalVragen + 2].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            ws.Cells[rowoffset + 2 + aantalStudenten, coloffset, rowoffset + 2 + aantalStudenten + offset, coloffset].Style.Border.Right.Style = ExcelBorderStyle.Thin;

            ws.Cells[rowoffset + 2 + aantalStudenten, coloffset, rowoffset + 2 + aantalStudenten + offset - 1, coloffset + aantalVragen + 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
            ws.Cells[rowoffset + 2 + aantalStudenten, coloffset, rowoffset + 2 + aantalStudenten + offset - 1, coloffset + aantalVragen + 2].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 238, 185));

            ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset, rowoffset + 2 + aantalStudenten + offset, coloffset + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
            ws.Cells[rowoffset + 2 + aantalStudenten + offset, coloffset, rowoffset + 2 + aantalStudenten + offset, coloffset + 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 238, 185));
            
        }

        private static void CreateMainArray(ExcelWorksheet ws,int rowoffset, int coloffset, List<Vraag> gewichten )
        {
            //Scoregewichten
            ws.Cells[rowoffset, coloffset].Value = "max score";
            
            for (int i = 1; i <= aantalVragen; i++)
            {
                ws.Cells[rowoffset, coloffset + i].Value = gewichten[i-1].Gewicht;
                ws.Cells[rowoffset, coloffset + i].Style.Locked = false;
            }
            
            ws.Cells[rowoffset, coloffset + aantalVragen + 1].Formula = "Sum(" + ws.Cells[rowoffset, coloffset + 1].Address + ":" +
                                                        ws.Cells[rowoffset, coloffset + aantalVragen].Address + ")";
             ws.Cells[rowoffset, coloffset + aantalVragen + 1].Style.Fill.PatternType= ExcelFillStyle.Solid;
            ws.Cells[rowoffset, coloffset + aantalVragen + 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255,238,185));
            ws.Cells[rowoffset, coloffset + aantalVragen + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            //Op 20
            ws.Cells[rowoffset, coloffset + aantalVragen + 2].Value = "20";
            ws.Cells[rowoffset, coloffset + aantalVragen + 2].Style.Font.Bold = true;
            ws.Cells[rowoffset, coloffset + aantalVragen + 2].Style.Locked = true;
            ws.Cells[rowoffset, coloffset + aantalVragen + 2].Style.HorizontalAlignment= ExcelHorizontalAlignment.Center;
            
            //vraagnummers
            var r = ws.Cells[rowoffset+1, coloffset];
            r.Value = "toetsitem";
            r.Style.Font.Bold = true;
            r.Style.VerticalAlignment= ExcelVerticalAlignment.Center;
            r.Style.HorizontalAlignment= ExcelHorizontalAlignment.Right;

            AddBorder(r, ExcelBorderStyle.Thin);

            for (int i = 1; i <= aantalVragen; i++)
            {
                var vraagr = ws.Cells[rowoffset + 1, coloffset + i];
                vraagr.Value ="vraag "+ i;
                if (gewichten[i - 1].IsMultipleChoice)
                    ws.Cells[rowoffset + 1 + aantalStudenten + 1, coloffset + i].Value = gewichten[i - 1].AantalMCVragen;

                if (gewichten[i - 1].VraagBeschrijving != string.Empty)
                {
                   vraagr.AddComment(gewichten[i - 1].VraagBeschrijving, "Vraagbeschrijving");
                }

                AddBorder(vraagr, ExcelBorderStyle.Thin);
                vraagr.Style.TextRotation = 90;
                vraagr.Style.HorizontalAlignment= ExcelHorizontalAlignment.Center;
            }

            ws.Cells[rowoffset + 1, coloffset + aantalVragen+1].Value = "toetscore";
            ws.Cells[rowoffset + 1, coloffset + aantalVragen + 1].Style.TextRotation = 90;
            ws.Cells[rowoffset + 1, coloffset + aantalVragen+2].Value = "/20";
            ws.Cells[rowoffset + 1, coloffset + aantalVragen+1].Style.HorizontalAlignment= ExcelHorizontalAlignment.Center;
            ws.Cells[rowoffset + 1, coloffset + aantalVragen + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            AddBorder(ws.Cells[rowoffset + 1, coloffset + aantalVragen + 1, rowoffset + 1, coloffset + aantalVragen + 2],ExcelBorderStyle.Thin);

            //Studentenpunten
            for (int i = 0; i < aantalStudenten; i++)
            {
                var rs = ws.Cells[rowoffset + 2 + i, coloffset + aantalVragen + 1];

                ws.Cells[rowoffset+2 + i, coloffset].Value = "Student " + i;
                ws.Cells[rowoffset + 2 + i, coloffset].Style.Locked = false;
                
                rs.Formula = "Sum(" + ws.Cells[rowoffset+2 + i, coloffset + 1].Address + ":" +
                                                                ws.Cells[rowoffset+2 + i, coloffset + aantalVragen].Address + ")";
                
                rs.Style.Fill.PatternType = ExcelFillStyle.Solid;
                rs.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 238, 185));
                AddBorder(rs,ExcelBorderStyle.DashDotDot);


                for (int j = 0; j < aantalVragen; j++)
                {
                    ws.Cells[rowoffset + 2 + i, coloffset + j + 1].Value = 0;
                    ws.Cells[rowoffset + 2 + i, coloffset+j+1].Style.Locked = false;
                }

                var rp = ws.Cells[rowoffset + 2 + i, coloffset + aantalVragen + 2];
                string round20 =string.Format("ROUND({0},0)", ws.Cells[rowoffset + 2 + i, coloffset + aantalVragen + 1].Address + "/" +
                    ExcelCellBase.GetAddress(rowoffset, true, coloffset + aantalVragen + 1, true) + "*20");

                rp.Formula = round20;
                    
                    
               
                rp.Style.Font.Bold = true;
                rp.Style.Fill.PatternType = ExcelFillStyle.Solid;
                rp.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 238, 185));
                AddBorder(rp, ExcelBorderStyle.DashDotDot);
            }

            ws.Cells[rowoffset + 1 + aantalStudenten + 1, coloffset].Value = "MC vraag:";

           
            
            System.Drawing.Image img = new Bitmap("Images\\Clipboard01.bmp");
            ExcelPicture pic = ws.Drawings.AddPicture("artesiscopyright", img);
            pic.From.Column = 0;
            pic.From.Row =rowoffset + 1 + aantalStudenten + 1;
          //  pic.From.ColumnOff = 3;
        //    pic.From.RowOff = 2;
            pic.SetSize(40);
        }

        private static void AddBorder(ExcelRange r, ExcelBorderStyle style)
        {
            r.Style.Border.Left.Style = style;
            r.Style.Border.Top.Style = style;
            r.Style.Border.Right.Style = style;
            r.Style.Border.Bottom.Style = style;
        }

        private static FileInfo CreateFile(string filename)
        {
            FileInfo newFile = new FileInfo(filename );
            if (newFile.Exists)
            {
                newFile.Delete(); // ensures we create a new workbook
                newFile = new FileInfo(filename );
            }
            return newFile;
        }
    }


    
  }
   
